﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for bdSQLServer
/// </summary>
public class bdSQLServer
{

    private string cadenaConexion;
    private SqlConnection cnx;

    /// <summary>
    /// 
    /// </summary>
    public bdSQLServer(string cadenaCnx)
    {
        this.cadenaConexion = cadenaCnx;
    }
    /// <summary>
    /// 
    /// </summary>
    public bdSQLServer()
    {

    }

    /// <summary>
    /// 
    /// </summary>
    /// <returns></returns>
    public void bdConecta()
    //public bdSQLServer bdConecta()
    {
        try
        {
            cnx = new SqlConnection(cadenaConexion);
            cnx.Open();
            //return new bdSQLServer(this.cadenaConexion);
            //return cnx;
        }
        catch (Exception ex)
        //catch (bdExcepcion ex)
        {
            //throw new bdExcepcion(ex.Message);
            throw (new Exception(ex.ToString()));
        }

    }
    /// <summary>
    /// 
    /// </summary>
    public void bdCierra()
    {
        if (cnx.State == System.Data.ConnectionState.Open)
        { cnx.Close(); }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="consultaSQL"></param>
    /// <returns></returns>
    public SqlDataReader bdConsulta(string consultaSQL)
    {
        SqlDataReader dr;
        SqlCommand cmd;

        try
        {
            cmd = new SqlCommand(consultaSQL, cnx);
            dr = cmd.ExecuteReader();
            return dr;
        }
        catch (Exception ex)
        //catch (bdExcepcion ex)
        {
            //throw new bdExcepcion(ex.Message);
            throw (new Exception(ex.ToString()));
        }

    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="ejecutaSQL"></param>
    public int bdEjecuta(string ejecutaSQL)
    {
        int iEjecuta;
        SqlCommand cmd;

        try
        {
            cmd = new SqlCommand(ejecutaSQL, cnx);
            iEjecuta = cmd.ExecuteNonQuery();
            return iEjecuta;
        }
        catch (Exception ex)
        //catch (bdExcepcion ex)
        {
            //throw new bdExcepcion(ex.Message);
            throw (new Exception(ex.ToString()));
        }
    }

    public DataSet dsConsulta(string query, string nombretabla)
    {
        DataSet funQuery = new DataSet();
        SqlDataReader reader = null;
        try
        {
            SqlCommand comando = new SqlCommand(query, cnx);
            DataTable tabQuery = new DataTable(nombretabla);
            reader = comando.ExecuteReader();
            int columnas = 0;
            int contColumnas = 0;
            int columnasModificable = 0;
            bool nullo = true;
            columnas = reader.FieldCount;
            columnasModificable = columnas;
            for (contColumnas = 0; contColumnas < columnas; contColumnas++)
                tabQuery.Columns.Add(new DataColumn(reader.GetName(contColumnas)));
            while (reader.Read())
            {
                DataRow filas = tabQuery.NewRow();
                for (contColumnas = 0; contColumnas < columnas; contColumnas++)
                {
                    nullo = reader.IsDBNull(contColumnas);
                    if (nullo == false)
                    {
                        switch (reader.GetDataTypeName(contColumnas).ToString().ToUpper())
                        {

                            case "UNIQUEIDENTIFIER":
                                filas[reader.GetName(contColumnas)] = reader.GetGuid(contColumnas);
                                break;
                            case "BIGINT":
                                filas[reader.GetName(contColumnas)] = reader.GetInt64(contColumnas);
                                break;
                            case "INT":
                                filas[reader.GetName(contColumnas)] = reader.GetInt32(contColumnas);
                                break;
                            case "VARCHAR":
                                filas[reader.GetName(contColumnas)] = reader.GetString(contColumnas);
                                break;
                            case "NCHAR":
                                filas[reader.GetName(contColumnas)] = reader.GetString(contColumnas);
                                break;
                            case "TINYINT":
                                filas[reader.GetName(contColumnas)] = reader.GetBoolean(contColumnas);
                                break;
                            case "DATETIME":
                                filas[reader.GetName(contColumnas)] = reader.GetDateTime(contColumnas);
                                break;
                            case "TIME":
                                filas[reader.GetName(contColumnas)] = reader.GetDateTime(contColumnas);
                                break;
                            case "DATE":
                                filas[reader.GetName(contColumnas)] = reader.GetDateTime(contColumnas);
                                break;
                            case "DOUBLE":
                                filas[reader.GetName(contColumnas)] = reader.GetDouble(contColumnas);
                                break;
                            case "MONEY":
                                filas[reader.GetName(contColumnas)] = reader.GetDouble(contColumnas);
                                break;
                            case "FLOAT":
                                filas[reader.GetName(contColumnas)] = reader.GetFloat(contColumnas);
                                break;
                            case "BLOB":
                                filas[reader.GetName(contColumnas)] = reader.GetString(contColumnas);
                                break;
                            default:
                                filas[reader.GetName(contColumnas)] = reader.GetString(contColumnas);
                                break;
                        }
                    }
                    else
                        filas[reader.GetName(contColumnas)] = "NULL";
                }
                tabQuery.Rows.Add(filas);
            }
            funQuery.Tables.Add(tabQuery);
            reader.Close();
            return funQuery;
        }

        catch (Exception ex)
        {
            reader.Close();
            throw new Exception(ex.Message);
        }

    }

    public DataSet dataSetConsulta(string strQuery, string strTabla)
    {
        SqlDataAdapter adp;
        DataSet ds = new DataSet();
        try
        {
            adp = new SqlDataAdapter(strQuery, cnx);
            adp.Fill(ds, strTabla);
        }
        catch (Exception ex)
        {
            /*
            clasegenerica<string> g = new clasegenerica<string>();
            g.atributo = "algo";
            g.atributo2 = "";*/

            throw new Exception(ex.Message);
        }

        return ds;
    }


}